/**
 * 多表查询
 */
const { getWhereStr } = require("../_utils");
const { N, needParam, splitModChar, splitAliasChar, consoleLog, typeOf } = require("../../../utils");
const { standTables } = require("../../../../../../app/tables-data");
const { getSomeFieldNames, getStandFieldNames } = require("../../../compile/mysql/_utils");
const { toStandardSqlField } = require("../../../compile/mysql/_utils");
const { E_NoticeType } = require("../../../../../../utils/const");

// module.exports = {
//   getStandTablesSelect() {
//     return [
//       { name: "user", fields: ["*"], excludes: ["password"] },
//       "LEFt JOIN user.id = note.create_id",
//       { name: "note", fields: ["*"], excludes: "remark" },
//     ];
//   },
// };

// `SELECT n.*,
// os_praise.status AS is_praise,
// os_belittle.status AS is_belittle,
// os_collect.status AS is_collect,
// os_share.status AS is_share,
// os_report.status AS is_report
// FROM note n
// LEFT JOIN operate_status os_praise ON os_praise.create_id = n.id AND os_praise.type = 12
// LEFT JOIN operate_status os_belittle ON os_belittle.create_id = n.id AND os_belittle.type = 13
// LEFT JOIN operate_status os_collect ON os_collect.create_id = n.id AND os_collect.type = 14
// LEFT JOIN operate_status os_share ON os_share.create_id = n.id AND os_share.type = 15
// LEFT JOIN operate_status os_report ON os_report.create_id = n.id AND os_report.type = 18
// WHERE n.id = '1';`
const tempTables = [
  {
    "user:u": [
      // "*",
      "nickname",
      "password:psd",
      // `(SELECT status FROM operate_status WHERE operate_status.create_id = note.create_id AND type = ${E_NoticeType.praise}) AS is_praise`,
    ],
  },
  {
    "note:n|LEFT JOIN note n ON u.id = n.create_id": [
      // "*|remark,id",
      "remark",
    ],
  },
  // { "note:n": ["*|remark"], "join|LEFT": { "u.id": "n.create_id" } },
  // { "note:n": ["*|remark"], join: { "u.id": "n.create_id" } },
];
module.exports = {
  // 获取字段的sql字符串
  // getFieldsStr() {
  //   // modName 带修饰符的表单名称
  //   const fieldsStr = tableNames
  //     .map((modName, ind) => {
  //       const [tNameStr] = modName.split(splitModChar);
  //       const [tName, aName] = tNameStr.split(splitAliasChar); // tName真实表名 aName真实表名的别名
  //       const { sqlFields } = standTables[tName];
  //       const isMain = ind === 0;
  //       // 考虑要转换日期格式，所以需要把['*']拆开成多个字段
  //       let fieldNames = isMain ? mainFieldNames : getStandFieldNames(tableMap[modName] ?? ["*"]);
  //       // let fieldNames = isMain ? mainFieldNames : tableMap[modName];
  //       // 后面的join表，要去除掉第一个关联主键
  //       if (isMain) {
  //         if (!isSingle) {
  //           if (!fieldNames[0]) throw new Error(`主表${tName}主键不能为空`);
  //           const { isPrimaryKey, name: fieldName } = toStandardSqlField(fieldNames[0]);
  //           if (!isPrimaryKey) throw new Error(`主表${tName}第一个键必须为主键，当前为：${fieldName}`);
  //         }
  //       } else {
  //         if (!fieldNames?.length) throw new Error(`从表${tName}主键不能为空`);
  //         const [equalStr, ...restFields] = fieldNames;
  //         joinTableMap[modName] = equalStr;
  //         // 考虑要转换日期格式，所以需要把['*']拆开成多个字段
  //         // 如果指定['*']，则为查询子表的其余所有字段（除关联主键外）。
  //         const subMainKey = equalStr
  //           .split("=")
  //           .map(it => it.trim())[1]
  //           .split(".")[1];
  //         fieldNames = getStandFieldNames(restFields, tName, [subMainKey]);
  //       }
  //       return (
  //         fieldNames
  //           .map((fieldMod, i) => {
  //             const t = typeOf(fieldMod);
  //             if (t === "String") {
  //               // 从表的第一个字段为 user.create_id=user.id 形式，故不参与处理
  //               // if (!isMain && i === 0) return "";
  //               let [field, aliasName] = fieldMod.split(splitAliasChar); // splitAliasChar 后面的是别名
  //               const fieldInfo = sqlFields.find(it => it.name === field);
  //               if (!fieldInfo) throw new Error(`${tName}表中不存在字段：${field}`);
  //               const { type } = fieldInfo;
  //               if (type === "timestamp" || type === "datetime") {
  //                 field = `DATE_FORMAT(${isSingle ? field : `${aName ?? tName}.${field}`}, '%Y-%m-%d %H:%i:%s') AS ${
  //                   isSingle || isMain ? field : `${tName}_${field}`
  //                 }`;
  //                 return field;
  //               }
  //               if (isSingle) return field;
  //               let subField = `${aName ?? tName}.${field}`;
  //               if (aliasName) subField += ` as ${aliasName}`;
  //               return subField;
  //             }
  //             if (t === "Array") {
  //               return fieldMod.join(" AS ");
  //             }
  //             // if(t==="Object"){
  //             // }
  //             throw new Error(`暂未处理此类型：${t}`);
  //           })
  //           // .filter(it => !!it.trim())
  //           .join(", ")
  //       );
  //     })
  //     .join(`,${N}`);
  // },
  // 获取字段的sql字符串
  getFieldsStr(fieldNames = [], { isSingle, isMain, tName, aName }) {
    // modName 带修饰符的表单名称
    // const fieldsStr = tableNames
    //   .map((modName, ind) => {
    // const [tNameStr] = modName.split(splitModChar);
    // const [tName, aName] = tNameStr.split(splitAliasChar); // tName真实表名 aName真实表名的别名
    // const { sqlFields } = standTables[tName];
    // const isMain = ind === 0;
    // // 考虑要转换日期格式，所以需要把['*']拆开成多个字段
    // let fieldNames = isMain ? mainFieldNames : getStandFieldNames(tableMap[modName] ?? ["*"]);
    // // let fieldNames = isMain ? mainFieldNames : tableMap[modName];
    // // 后面的join表，要去除掉第一个关联主键
    // if (isMain) {
    //   if (!isSingle) {
    //     if (!fieldNames[0]) throw new Error(`主表${tName}主键不能为空`);
    //     const { isPrimaryKey, name: fieldName } = toStandardSqlField(fieldNames[0]);
    //     if (!isPrimaryKey) throw new Error(`主表${tName}第一个键必须为主键，当前为：${fieldName}`);
    //   }
    // } else {
    //   if (!fieldNames?.length) throw new Error(`从表${tName}主键不能为空`);
    //   const [equalStr, ...restFields] = fieldNames;
    //   joinTableMap[modName] = equalStr;
    //   // 考虑要转换日期格式，所以需要把['*']拆开成多个字段
    //   // 如果指定['*']，则为查询子表的其余所有字段（除关联主键外）。
    //   const subMainKey = equalStr
    //     .split("=")
    //     .map(it => it.trim())[1]
    //     .split(".")[1];
    //   fieldNames = getStandFieldNames(restFields, tName, [subMainKey]);
    // }
    return (
      fieldNames
        .map((fieldMod, i) => {
          // const t = typeOf(fieldMod);
          // if (t === "String") {
          // 从表的第一个字段为 user.create_id=user.id 形式，故不参与处理
          // if (!isMain && i === 0) return "";

          // 如果有空格，则是sql语句，直接返回
          if (fieldMod.indexOf(" ") !== -1) return fieldMod;
          const { sqlFields } = standTables[tName];
          let [field, aliasName] = fieldMod.split(splitAliasChar); // splitAliasChar 后面的是别名
          const fieldInfo = sqlFields.find(it => it.name === field);
          if (!fieldInfo) throw new Error(`${tName}表中不存在字段：${field}`);
          const { type } = fieldInfo;
          if (type === "timestamp" || type === "datetime") {
            field = `DATE_FORMAT(${isSingle ? field : `${aName ?? tName}.${field}`}, '%Y-%m-%d %H:%i:%s') AS ${
              isSingle || isMain ? field : `${tName}_${field}`
            }`;
            return field;
          }
          if (isSingle) return field;
          let subField = `${aName ?? tName}.${field}`;
          if (aliasName) subField += ` AS ${aliasName}`;
          return subField;
          // }
          // if (t === "Array") {
          //   return fieldMod.join(" AS ");
          // }
          // // if(t==="Object"){
          // // }
          // throw new Error(`暂未处理此类型：${t}`);
        })
        // .filter(it => !!it.trim())
        .join(", ")
    );
    // })
    // .join(`,${N}`);
  },
  // 获取JOIN 链接的sql字符串
  // joinInfoStr 示例：LEFT JOIN {u.id: 'n.create_id'}
  getJoinStr(joinInfoStr = "", tName, aName) {
    // LEFT JOIN {u.id: 'n.create_id'}
    // const [type, where] = joinInfoStr.split(/\s+JOIN\s+/i);
    // console.log(joinInfoStr, type, where, JSON.parse(where), "where---------------");
    // const whereStr = getWhereStr(where, []);
    // const name = aName ?? tName;
    // return `${type.toUpperCase()} JOIN ${tName} ${aName} ON ${whereStr}`;
    return `${type.toUpperCase()} JOIN ${tName} ${aName} ON ${whereStr}`;
  },
  // 获取标准的字段信息
  getStandField(simpleFields = [], tableName) {
    let isDeep = false;
    const arr = simpleFields.map(field => {
      if (field.startsWith("*")) {
        isDeep = true;
        const [allFieldsStr, excludesStr] = field.split("|");
        const allFields = getStandFieldNames(["*"], tableName);
        if (!excludesStr) return allFields;
        const excludeFields = excludesStr.split(",");
        excludeFields.forEach(item => {
          const findInd = allFields.findIndex(it => it === item);
          if (findInd !== -1) allFields.splice(findInd, 1);
        });
        return allFields;
      }
      return field;
    });
    return isDeep ? arr.flat(1) : arr;
  },
  // 获取多表查询的sql语句
  getTablesSelectSql(tables = tempTables) {
    // 如果是表名称，则转成多表的数据结构
    if (typeof tables === "string") {
      tables = [{ [tables]: getStandFieldNames(["*"], tables) }];
    }
    if (!tables?.length) return "";
    const { getFieldsStr, getJoinStr, getStandField } = module.exports;
    const isSingle = tables.length === 1;
    const nChar = isSingle ? " " : N;
    let fieldsStr = "";
    let joinStr = "";
    let mainName = ""; // 主表名称
    let mainAlias = ""; // 主表的别名
    tables.forEach((table, ind) => {
      const [[tableInfo, simpleFields]] = Object.entries(table);
      const [tableName, joinInfoStr] = tableInfo.split(splitModChar);
      const [tName, aName] = tableName.split(splitAliasChar);
      const standFields = getStandField(simpleFields, tName);
      const isMain = ind === 0;
      const fieStr = getFieldsStr(standFields, { isSingle, isMain, tName, aName });
      if (fieStr) fieldsStr += `${fieldsStr ? `,${N}` : ""}${fieStr}`;
      if (isMain) {
        mainName = tName;
        if (aName) mainAlias = aName;
        return;
      }
      // const jStr = getJoinStr(joinInfoStr, tName, aName);
      const jStr = joinInfoStr;
      if (jStr) joinStr += `${joinStr ? nChar : ""}${jStr}`;
    });
    return `SELECT${nChar}${fieldsStr}${nChar}FROM ${mainName} ${mainAlias}${nChar}${joinStr}`;
  },
};
